﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using SqlServer = DataCoreTechnologies.SqlServer;

namespace DataCoreTechnologies.Scribe.Business.Html
{
    internal class TableDocument : Document
    {
        #region Public
        //Constructor
        internal TableDocument(SqlServer.Table table)
        {
            DocumentType = DocumentType.Table;
            RootDatabase = table.Schema.Database;
            RootServer = RootDatabase.Server;
            URL = "index." + RootDatabase.Name + "." + table.Schema.Name + ".table." + table.Name + @".html";
            _table = table;
        }
        //Properties
        //Methods
        internal override string DisplayName { get { return _table.Name; } }
        internal override string GetHtml()
        {
            if (_html == "")
            {
                Boolean evenRow = true;

                AddDdlCssColors();
                AddCssLine(@"      	.propertyColumn {width: 300px; word-wrap: break-word;}");
                AddCssLine(@"      	.nameColumn {width: 300px; word-wrap: break-word;}");

                AddCssLine(@"      	.columnColumn {width: 15%; word-wrap: break-word;}");
                AddCssLine(@"      	.createDateColumn {width: 10%; word-wrap: break-word;}");
                AddCssLine(@"      	.modifyDateColumn {width: 10%; word-wrap: break-word;}");
                AddCssLine(@"      	.isClusteredColumn {width: 20%; word-wrap: break-word;}");
                AddCssLine(@"      	.typeColumn {width: 10%; word-wrap: break-word;}");
                AddCssLine(@"      	.allowNullsColumn {width: 6%; word-wrap: break-word;}");
                AddCssLine(@"      	.identityColumn {width: 6%; word-wrap: break-word;}");
                AddCssLine(@"      	.isForeignKeyColumn {width: 6%; word-wrap: break-word;}");
                AddCssLine(@"      	.defaultColumn {width: 10%; word-wrap: break-word;}");

                AddBodyLine(@"<div id=""mainBody"">");

                string heirarchy = @"<h2 id=""topNav"">Location: <a href=""http://index.html"">" + RootServer.Name +
                    @"</a> . ";
                heirarchy += @"<a href=""http://index." +
                    RootDatabase.Name + @".html"">" + ScrubHtml(RootDatabase.Name) + @"</a> . ";
                
                if (Business.FilterPreferences.ShowSchemasPage)
                  heirarchy += @"<a href=""http://index." +
                    RootDatabase.Name + @".schemas.html"">" + ScrubHtml(_table.Schema.Name) + @"</a> . ";
                else
                   heirarchy += ScrubHtml(_table.Schema.Name);
                
                heirarchy += _table.Name + @"</h2>";
                AddBodyLine(heirarchy);
                AddBodyLine(@"<h2 id=""title"">" + ScrubHtml(_table.Name) + @" (Table)</h2>");

                #region Lambda
                Func<string, string, string> tableRow = (string property, string value) =>
                {
                    string returnValue = @"<tr class=""""><td>" + property + @"</td><td>";
                    if (evenRow == true)
                        returnValue = returnValue.Replace(@"class=""""", @"class=""evenRow""");
                    else
                        returnValue = returnValue.Replace(@"class=""""", @"class=""oddRow""");

                    if (value == null | value == "")
                        returnValue += @"&nbsp;";
                    else
                        returnValue += value;
                    returnValue += @"</td></tr>";

                    evenRow = !evenRow;
                    return returnValue;
                };
                #endregion
                #region Properties
                evenRow = true;
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_properties"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('properties');"">Properties</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""properties"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Property</th><th>Value</th></tr>");
                AddBodyLine(@"<colgroup><col class=""propertyColumn"" /><col class=""valueColumn"" /></colgroup>");

                AddBodyLine(tableRow("Name", _table.Name));
                if (Business.FilterPreferences.ShowSchemasPage)
                  AddBodyLine(tableRow("Schema", @"<a href=""http://index." +
                    RootDatabase.Name + @".schemas.html"">" + ScrubHtml(_table.Schema.Name) + @"</a>"));
                else
                   AddBodyLine(tableRow("Schema", ScrubHtml(_table.Schema.Name)));
                AddBodyLine(tableRow("Data Size", AddCommasToNumericString(_table.DataSpaceUsed.ToString()) + " KB"));
                AddBodyLine(tableRow("Index Size", AddCommasToNumericString(_table.IndexSpaceUsed.ToString()) + " KB"));
                AddBodyLine(tableRow("Creation Date", _table.CreateDate.ToString()));
                if (_table.ModifiedDate == null)
                    AddBodyLine(tableRow("Last Modified", "N/A"));
                else
                    AddBodyLine(tableRow("Last Modified", _table.ModifiedDate.ToString()));
                AddBodyLine(tableRow("Description", ScrubHtml(_table.Description)));

                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Columns
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_columnsDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('columnsDiv');"">Columns</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""columnsDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Type</th><th>Allow Nulls</th><th>Is Primary Key</th><th>Is Foreign Key</th><th>Default</th><th>Description</th></tr>");
                string codeLine = @"<colgroup><col class=""nameColumn"" /><col class=""typeColumn"" />" +
                    @"<col class=""allowNullsColumn"" /><col class=""identityColumn"" /><col class=""isForeignKeyColumn"" /><col class=""defaultColumn"" />" +
                    @"<col class=""descriptionColumn"" /></colgroup>";
                AddBodyLine(codeLine);

                evenRow = true;
                foreach (SqlServer.Column column in _table.Columns)
                {
                    string row = "";
                    row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;

                    row += @"<td>" + ScrubHtml(column.Name) + @"</td>";
                    row += @"<td>" + ScrubHtml(DisplayColumn(column)) + @"</td>";
                    row += @"<td>" + column.IsNullable.ToString() + @"</td>";
                    row += @"<td>" + column.IsPrimaryKey.ToString() + @"</td>";
                    row += @"<td>" + column.IsForeignKey.ToString() + @"</td>";
                    if (column.DefaultValue == null)
                        row += @"<td>&nbsp;</td>";
                    else
                        row += @"<td>" + ScrubHtml(column.DefaultValue.Formula) + @"</td>";
                    if (column.Description == "")
                        row += @"<td>&nbsp;</td>";
                    else
                        row += @"<td>" + ScrubHtml(column.Description) + @"</td>";
                    row += @"</tr>";
                    AddBodyLine(row);
                }
                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Triggers
                evenRow = true;
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_triggersDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('triggersDiv');"">Triggers</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""triggersDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Instead Of</th><th>Insert</th><th>Update</th><th>Delete</th></tr>");
                AddBodyLine(@"<colgroup><col class=""nameColumn"" /><col class=""insteadOfColumn"" />" +
                            @"<col class=""insertColumn"" /><col class=""updateColumn"" /><col class=""deleteColumn"" />" +
                            @"</colgroup>");
                foreach (SqlServer.Trigger trigger in _table.Triggers)
                {
                   string row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                   evenRow = !evenRow;

                   if (Business.FilterPreferences.ShowTriggersPage)
                   {
                      row += @"<td><a href=""http://index." + RootDatabase.Name
                              + @"." + trigger.Table.Schema.Name + "." + trigger.Table.Name
                              + @".trigger." + trigger.Name + @".html"">"
                              + ScrubHtml(trigger.Name) + @"</a></td>";
                   }
                   else
                   {
                      row += @"<td>" + ScrubHtml(trigger.Name) + @"</td>";
                   }

                   row += @"<td>" + trigger.IsInsteadOfTrigger.ToString() + @"</td>";
                   row += @"<td>" + trigger.IsInsert.ToString() + @"</td>";
                   row += @"<td>" + trigger.IsUpdate.ToString() + @"</td>";
                   row += @"<td>" + trigger.IsDelete.ToString() + @"</td>";

                   row += @"</tr>";
                   AddBodyLine(row);
                }
                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Indexes
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_indexesDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('indexesDiv');"">Indexes</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""indexesDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Clustered</th><th>Column(s)</th></tr>");
                AddBodyLine(@"<colgroup><col class=""nameColumn"" /><col class=""isClusteredColumn"" /><col class=""columnsColumn"" /></colgroup>");

                evenRow = true;
                foreach (SqlServer.Index index in _table.Indexes)
                {
                    string row = "";
                    row += evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;

                    if (Business.FilterPreferences.ShowIndexesPage)
                    {
                       row += @"<td><a href=""http://index." +
                           RootDatabase.Name + @"." + index.Table.Schema.Name + "." +
                           index.Table.Name + ".index." + index.Name + @".html"">" +
                           ScrubHtml(index.Name) + @"</a></td>";
                    }
                    else
                    {
                       row += @"<td>" + ScrubHtml(index.Name) + @"</td>";
                    }

                    row += @"<td>" + index.Type.ToString() + @"</td>";
                    row += @"<td>&nbsp;";

                    foreach (SqlServer.IndexColumn indexColumn in index.IndexColumns)
                    {
                        row += ScrubHtml(indexColumn.Column.Name);
                        if (indexColumn.IsDescending == true) row += " desc";
                        row += ", ";
                    }
                    row = row.TrimEnd(", ".ToCharArray());

                    row += @"</td></tr>";

                    AddBodyLine(row);
                }
                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Keys
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_keysDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('keysDiv');"">Keys</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""keysDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Type</th><th>Parent Columns</th><th>Reference Columns</th></tr>");
                AddBodyLine(@"<colgroup><col class=""nameColumn"" /><col class=""typeColumn"" />" +
                            @"<col class=""parentColumn"" /><col class=""referenceColumn"" /></colgroup>");

                evenRow = true;
                if (_table.PrimaryKey != null)
                {
                    string row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;
                    row += @"<td>" + ScrubHtml(_table.PrimaryKey.Name) + @"</td><td>Primary Key</td><td>N/A</td><td>";
                    if (_table.PrimaryKey != null)
                    {
                        foreach (SqlServer.IndexColumn indexColumn in _table.PrimaryKey.Index.IndexColumns)
                            row += ScrubHtml(indexColumn.Index.PrimaryKey.Table.Name) 
                                + "." + ScrubHtml(indexColumn.Column.Name) + @", ";
                        if (row.Substring(row.Length - 2, 2) == ", ") row = row.Substring(0, row.Length - 2);
                    }
                    row += @"</td></tr>";
                    AddBodyLine(row);
                }
                foreach (SqlServer.ForeignKey fk in _table.ForeignKeys)
                {
                    string row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;
                    row += @"<td>" + fk.Name + @"</td><td>Foreign Key</td><td>";
                    foreach (SqlServer.ForeignKeyColumns fkc in fk.ForeignKeyColumns)
                    {
                        row += ScrubHtml(fkc.ParentColumn.Name) + ", ";
                    }
                    if (row.Substring(row.Length - 2, 2) == ", ") row = row.Substring(0, row.Length - 2);

                    row += @"</td><td>";
                    foreach (SqlServer.ForeignKeyColumns fkc in fk.ForeignKeyColumns)
                    {
                        row += @"<a href=""http://index." +
                            RootDatabase.Name + @"." + fk.Table.Schema.Name + ".table." +
                            fkc.ReferenceColumn.Table.Name + @".html"">";
                        row += ScrubHtml(fkc.ReferenceColumn.Table.Name) + "." + ScrubHtml(fkc.ReferenceColumn.Name) + @"</a>, ";
                    }
                    if (row.Substring(row.Length - 2, 2) == ", ") row = row.Substring(0, row.Length - 2);

                    row += @"</td></tr>";
                    AddBodyLine(row);
                }

                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Check Constraints
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_checkConstraintsDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('checkConstraintsDiv');"">Check Constraints</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""checkConstraintsDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Column</th><th>Create Date</th><th>Modify Date</th>" +
                            @"<th>Formula</th></tr>");
                AddBodyLine(@"<colgroup><col class=""nameColumn"" /><col class=""columnColumn"" />" +
                            @"<col class=""createDateColumn"" /><col class=""modifyDateColumn"" />" +
                            @"<col class=""formulaColumn"" /></colgroup>");

                evenRow = true;
                var checkConstraints = (from column in _table.Columns
                                        where column.CheckConstraint != null
                                        orderby column.CheckConstraint.Name
                                        select column.CheckConstraint);
                foreach (SqlServer.CheckConstraint cc in checkConstraints)
                {
                    string row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;
                    row += @"<td>" + ScrubHtml(cc.Name) + @"</td>";
                    row += @"<td>" + ScrubHtml(cc.Column.Name) + @"</td>";
                    row += @"<td>" + cc.CreateDate.ToString() + @"</td>";
                    row += @"<td>" + cc.ModifyDate.ToString() + @"</td>";
                    row += @"<td>" + ScrubHtml(cc.Formula) + @"</td>";
                    row += @"</tr>";
                    AddBodyLine(row);
                }

                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region Extended Properties
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_extPropertiesDiv"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('extPropertiesDiv');"">Extended Properties</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""extPropertiesDiv"">");
                AddBodyLine(@"<table>");
                AddBodyLine(@"<tr><th>Name</th><th>Value</th></tr>");
                AddBodyLine(@"<colgroup><col class=""nameColumn"" /><col class=""valueColumn"" /></colgroup>");

                evenRow = true;
                foreach (SqlServer.ExtendedProperty extProperty in _table.ExtendedProperties)
                {
                    string row = evenRow == true ? @"<tr class=""evenRow"">" : @"<tr>";
                    evenRow = !evenRow;
                    row += @"<td>" + ScrubHtml(extProperty.Name) + @"</td><td>" + ScrubHtml(extProperty.Value) + @"</td></tr>";
                    AddBodyLine(row);
                }

                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion
                #region DDL
                AddBodyLine(@"<h2>");
                AddBodyLine(@"<img id=""img_ddl"" src=""" + RootFolderPath + @"minus.jpg""> ");
                AddBodyLine(@"<a href=""javascript:;"" onmousedown=""toggleDiv('ddl');"">DDL</a>");
                AddBodyLine(@"</h2>");

                AddBodyLine(@"<div id=""ddl"">");
                AddBodyLine(@"<table class=""sqlCode"">");

                evenRow = true;
                var codeLines = ColorizeDDL(_table.GetDDLTokens());
                foreach (string line in codeLines)
                {
                    string code = "";
                    if (line == "")
                        code = @"&nbsp;";
                    else
                        code = line;

                    if (evenRow == true)
                        AddBodyLine(@"<tr class=""evenRow""><td>" + code + @"</td></tr>");
                    else
                        AddBodyLine(@"<tr class=""oddRow""><td>" + code + @"</td></tr>");
                    evenRow = !evenRow;
                }
                AddBodyLine(@"</table>");
                AddBodyLine(@"</div>");
                #endregion

                AddBodyLine(@"</div>");
                _html = CreateHtmlDocument();
            }
            return _html;
        }
        #endregion
        #region Private
        private SqlServer.Table _table;
        #endregion
    }
}
